﻿using System.Data;
using System.Text;
using Dapper;
using System.Data.Odbc;
using System.Data.Common;
using AutomaticPush.Extension;

namespace AutomaticPush.Common
{
    public class OdbcTools
    {


        public static int Execute(string conString, string sql)
        {
            using IDbConnection connection = new OdbcConnection(conString);
            return connection.Execute(sql);
        }



        /// <summary>
        /// 返回Dt
        /// </summary>
        /// <param name="conString"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteTable(string conString, string sql)
        {
            using IDbConnection connection = new OdbcConnection(conString); ;
            DataTable table = new DataTable();
            var reader = connection.ExecuteReader(sql);
            table.Load(reader);
            return table;
        }



        public List<T> ExecuteTable<T>(string conString, string sql)
        {
            using IDbConnection connection = new OdbcConnection(conString); ;
            DataTable table = new DataTable();
            var reader = connection.ExecuteReader(sql);
            table.Load(reader);
            return table.GetEntityList<T>();
        }

        
        /// <summary>
        /// 添加.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Add<T>(string conString, string sql, T t) where T : class
        {

            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 添加.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        public static int Add(string conString, string sql, object t = null)
        {
            using IDbConnection connection = new OdbcConnection(conString);

            return connection.Execute(sql, t);
        }




        /// <summary>
        /// 批量添加.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Add<T>(string conString, string sql, List<T> t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 删除.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Delete<T>(string conString, string sql, T t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 批量删除.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Delete<T>(string conString, string sql, List<T> t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 修改.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入实体类型.</param>
        /// <returns>int.</returns>
        public static int Update<T>(string conString, string sql, T t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 批量修改.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>int.</returns>
        public static int Update<T>(string conString, string sql, List<T> t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Execute(sql, t);
            }
        }

        /// <summary>
        /// 查询.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string conString, string sql) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Query<T>(sql).ToList();
            }
        }

        /// <summary>
        /// 查询指定数据.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <param name="t">传入泛型类.</param>
        /// <returns>类.</returns>
        public static T Query<T>(string conString, string sql, T t) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Query<T>(sql, t).SingleOrDefault();
            }
        }

        /// <summary>
        /// 查询的in操作.
        /// </summary>
        /// <typeparam name="T">实体类型.</typeparam>
        /// <param name="sql">传入sql执行语句.</param>
        /// <returns>泛型类.</returns>
        public static List<T> Query<T>(string conString, string sql, int[] ids) where T : class
        {
            using (IDbConnection connection = new OdbcConnection(conString))
            {
                return connection.Query<T>(sql, new { ids }).ToList();
            }
        }

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列</param>
        /// <param name="tableName">表</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList<T>(IDbConnection conn, string files, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total)
        {
            int skip = 1;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize + 1;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", files, tableName, where, orderby, skip, pageIndex * pageSize);
            using var reader = conn.QueryMultiple(sb.ToString());
            total = reader.ReadFirst<int>();
            return reader.Read<T>();
        }

    }
}
